﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.OleDb;
using PPAP.BO;
using PPAP.Model;
using System.Text.RegularExpressions;

namespace PPAP.Web.Sampling
{
    
    public partial class ImportPart : PageBase
    {
        SupplierBO bo = new SupplierBO();
        DataTable data = new DataTable();
        PARTBO pbo = new PARTBO();
        PPARBO ppbo = new PPARBO();
        LogBO lbo = new LogBO();
        UsersBO ubo = new UsersBO();
        MailTemplateBO mbo = new MailTemplateBO();
        SupplierAdminBO sabo = new SupplierAdminBO();
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        /// <summary>
        /// QE
        /// </summary>
        public class qe
        {
            public string name { get; set; }
            public int num { get; set; }
        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            string filePath = string.Empty;
            try
            {
                filePath = UploadFile.FileName;
                string name = filePath.Substring(filePath.LastIndexOf("\\") + 1).ToString();
                string exp = name.Substring(name.LastIndexOf(".") + 1);

                if (exp.ToLower() != "xls" && exp.ToLower() != "xlsx" && exp.ToLower() != "csv")
                {
                    ClientScript.RegisterStartupScript(this.GetType(), "key1", "<script>alert('File format not(.xls|.xlsx|.csv)！');</script>");
                }
                else
                {
                    string FileNam_Time = "";
                    string FName = DateTime.Now.ToString("yyyyMMddHHmmss")+name;
                    FileNam_Time = FName;//日期和名称组合
                    string path = HttpContext.Current.Request.MapPath("../FileUpload/ImportExcel/" + base.CurrentUser.Name + "/");
                    string url = "../FileUpload/QYRD/" + CurrentUser.Name + "/new" + FileNam_Time;
                    DirectoryInfo upDir = new DirectoryInfo(path);
                    if (!upDir.Exists)
                    {
                        upDir.Create();
                    }
                    filePath = path + FileNam_Time;
                    UploadFile.SaveAs(filePath);
                    ImportExcel(filePath);

                }
            }
            catch (Exception ex)
            {
                AlertMessage("Err："+ex.Message.ToString());
                return;
            }
            finally
            {
                
            }

        }

        private void ImportExcel(string excelFile)
        {
            DataSet ds = new DataSet();
            OleDbConnection myConn = new OleDbConnection();
            try
            {
                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
                if(rbt2007.Checked)
                strCon = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+excelFile+@";Extended Properties=""Excel 12.0;HDR=YES;""";
                myConn = new OleDbConnection(strCon);
                myConn.Open();
                System.Data.DataTable schemaTable = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });     //获取表名

                if (schemaTable.Rows.Count > 0)
                {
                    string tablename = "Sheet1" + "$";//获到表名 
                    string sql = "select * from [" + tablename + "]";  //从工作表查询。
                    OleDbDataAdapter da = new OleDbDataAdapter(sql, myConn);
                    da.Fill(ds); 

                    DataTable dt = new DataTable();
                    dt.Columns.Add("rownum");  //rownum
                    dt.Columns.Add("name1");   //BDNT Part No.          BDNT Part Name
                    dt.Columns.Add("name2");   //BDNT Part Name         BDNT Part No.
                    dt.Columns.Add("name3");   //Supplier Code          BDNT Revision No.
                    dt.Columns.Add("name4");   //BDNT Revision No.      BDNT Revision Status
                    dt.Columns.Add("name5");   //BDNT Revision Status   Supplier Code 
                    dt.Columns.Add("name6");   //Color                  Planed PPAR date
                    dt.Columns.Add("name7");   //Planed PPAR date       Color   
                    dt.Columns.Add("name8");   //QE                     QE
                    dt.Columns.Add("name9");   //Supplier Admin         Supplier Admin
                    dt.Columns.Add("name10");  //结果                   结果
                    ds.Tables[0].Columns.Add("supplierid");
                    int num = 0;
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        num++;
                        DataRow r = dt.NewRow();
                        r[0] = num;                         //rownum
                        r[1] = dr[0]; //rownum              //part name
                        r[2] = dr[1];//part no              //part no
                        r[3] = dr[2];//part name            //revision no
                        r[4] = "";//supplier name        //revision status
                        r[5] = dr[3];//status               //supplier code
                        r[6] = dr[4]; //color               //ppar date
                        r[7] = dr[5]; //date                //color
                        r[8] = dr[6]; //qe                  //qe
                        r[9] = dr[7];//Supplier Admin       //admin

                        if (dr[0].ToString() == "" && dr[1].ToString() == "" && dr[2].ToString() == "" && dr[3].ToString() == "" && dr[4].ToString() == "")
                            continue;

                        string supplierid = Check(r[5].ToString().Trim());
                        string  bopp = Checkpp(r[2].ToString().Trim(),supplierid,dr[5].ToString().Trim(),dr[6].ToString().Trim(),r[9].ToString().Trim(),dr[2].ToString().Trim());
                        r[10] = bopp; //结果
                        dr["supplierid"] = bopp == "<font color='blue'>success</font>" ? supplierid :bopp=="<font color='blue'>BDNT Part already exists</font>"?"-1": "0";  //验证结果划分suupplierid
                        dt.Rows.Add(r);
                    }
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                    if (ds.Tables[0].Rows.Count > 0)
                        btnSave.Visible = true;
                    else
                        btnSave.Visible = false;
                }

                Session["dsexcel"] = ds;
                btnSave.Visible = true;
                ////var q=from p in ds.Tables[0] group p by p  ds.Tables[0].g
                //List<PartImport> list = new List<PartImport>();
                //foreach (DataRow dr in ds.Tables[0].Rows)
                //{
                //    PartImport im = new PartImport
                //    {
                //        Color = dr[5].ToString().Trim(),
                //        Partname = dr[2].ToString().Trim(),
                //        Partno = dr[1].ToString().Trim(),
                //        Qe = dr[7].ToString().Trim(),
                //        Revisionstatus = dr[4].ToString().Trim(),
                //        Supplieradmin = dr[8].ToString().Trim(),
                //        Suppliercode = dr[3].ToString().Trim()
                //    };
                //    list.Add(im);
                //}
                //var q = from p in list
                //        group p by p.Qe into g
                //        select new
                //        {
                //            g.Key,
                //            NumProducts = g.Count()
                //        };
                ////List<PartImport> list2 = q.ToList().ConvertAll<PartImport>(Items=>new PartImport({});
               
            }
            catch (Exception ex)
            {
                AlertMessage("Err:"+ex.Message.ToString());
                GC.Collect();
            }
            finally
            {
                myConn.Close();
                myConn.Dispose();
            }
        }

        public bool CheckEmail(string Email)
        {
            if (!getRegexInput(Email, @"\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"))
            {
                return false;
            }
            else
            {
                return true;
            }

        }

        public bool getRegexInput(string str, string regexStr)//str是要被验证的字符串，regexStr为正则表达式
        {
            Regex rStr = new Regex(regexStr);
            bool re = false;
            if (rStr.IsMatch(str))
            {
                re = true;
            }
            return re;
        }

        /// <summary>
        /// 验证SupplierCode
        /// </summary>
        /// <param name="SupplierCode"></param>
        /// <returns></returns>
        private string Check(string SupplierCode)
        {
            data = bo.GetList(" and SUPPLIERCODE='" + SupplierCode + "'").Tables[0];
            if (data.Rows.Count > 0)
            {
                return data.Rows[0]["id"].ToString();
                //return "<font color='blue'>success</font>";
            }
            else
                return "0";
                //return "<font color='red'>Supplier Name Does not exist</font>";
        }

        /// <summary>
        /// 验证QE，part，suppliercode,supplieradmin
        /// </summary>
        /// <param name="PartNo"></param>
        /// <param name="supplierid"></param>
        /// <param name="color"></param>
        /// <param name="qe"></param>
        /// <param name="supplieradmin"></param>
        /// <returns></returns>
        private string Checkpp(string PartNo,string supplierid,string color,string qe,string supplieradmin,string Revision)
        {
            string sqlcolor = color.Trim() == "" ? "color is null" : "color='"+color+"'";
            string sqlRevision = Revision.Trim() == "" ? "BDNTREVISIONNO is null" : "BDNTREVISIONNO='" + Revision + "'";
            if (pbo.GetList(" BDNTPARTNO='" + PartNo + "' and "+sqlcolor+" and supplierid=" + supplierid + " and "+sqlRevision+" and status<>9 ").Tables[0].Rows.Count > 0)  //先判断part是否已存在，不存在则验证其他各项合法性
                return "<font color='blue'>BDNT Part already exists</font>";
            else
            {
                if (ubo.GetList("  position='SQE' and USERNAME='" + qe.ToLower() + "' ").Tables[0].Rows.Count == 0)
                    return "<font color='red'>Q-engineer Does not exist</font>";
                else if (supplierid == "0")
                    return "<font color='red'>Supplier Code Does not exist</font>";
                else if (!CheckEmail(supplieradmin))
                    return "<font color='red'>Supplier Admin format wrong</font>";
                return CheckPartNo(PartNo) ? "<font color='blue'>success</font>" : "<font color='red'>Format is not standard（BDNT Part No.）</font>";
            }

        }

        /// <summary>
        /// 验证PartNo合法性
        /// </summary>
        /// <param name="PartNo"></param>
        /// <returns></returns>
        private bool CheckPartNo(string PartNo)
        {
            //if (PartNo.Length < 11)
            //    return false;
            //if (PartNo.Substring(0, 4).ToUpper() != "TIG-")
            //    return false;
            //else return true;
            return true;
        }

        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnSave_Click(object sender, EventArgs e)
        {
            string message = "";
            try
            {
                if (Session["dsexcel"] != null)
                {
                    List<qe> list = new List<qe>();  //初始化一个QE集合
                    PART p = new PART();
                    PPAR pp = new PPAR(); 
                    //
                    pbo.UpdatePartCheck();  //将part置为不存在状态
                    //
                    int num = 0;
                    bool isqlevel = false;  //判断qlevel是否需要加一（如果BDNTREVISIONNO不一样，其他一样，则生成新的ppar，QLEVEL加一）
                    DataSet dsq = new DataSet();
                    DataSet ds = Session["dsexcel"] as DataSet;
                    MailTemplate mail15 = mbo.GetModel(15);  //给供应商管理员发送用户名和密码
                    MailTemplate mail16 = mbo.GetModel(16); //给QE发送任务提醒
                    //string sendQE = "";  //已发送的QE人员
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        if (dr["supplierid"].ToString() != "0")
                        {
                            if (dr["supplierid"].ToString().Trim() != "-1")  //如果excel中的Part在系统中不存在，则继续操作，否则修改check
                            {
                                //
                                if (ubo.CheckEmailNotAdmin(dr[7].ToString().Trim().ToLower()))
                                {
                                    message += dr[7].ToString().Trim() + " Already exists \n";
                                }
                                else
                                {
                                    p.BDNTPARTNO = dr[1].ToString().Trim();
                                    p.BDNTPARTNAME = dr[0].ToString().Trim();
                                    p.BDNTREVISIONNO = dr[2].ToString().Trim();
                                    p.BDNTREVISIONSTATUS = "";
                                    p.COLOR = dr[5].ToString().Trim();
                                    p.Supplieradmin = dr[7].ToString().Trim().ToLower();
                                    pp.Planedppardate = DateTime.Parse(dr[4].ToString().Trim());
                                    pp.Qeuser = dr[6].ToString().Trim();
                                    p.CREATETIME = DateTime.Now;
                                    p.CREATEUSER = CurrentUser.Id;
                                    p.SUPPLIERID = int.Parse(dr["supplierid"].ToString().Trim());

                                    string sqlcolor = p.COLOR.Trim() == "" ? "color is null" : "color='" + p.COLOR + "'";
                                    dsq = pbo.GetList(" status<>9 and BDNTPARTNO='" + p.BDNTPARTNO + "' and " + sqlcolor + " and supplierid=" + p.SUPPLIERID + " order by qlevel desc ");
                                    if (dsq.Tables[0].Rows.Count > 0)
                                    {
                                        if (dsq.Tables[0].Rows[0]["qlevel"].ToString().Trim() == "")
                                            pp.QLEVEL = "002";
                                        else
                                        {
                                            int a = int.Parse(dsq.Tables[0].Rows[0]["qlevel"].ToString());
                                            a++;
                                            pp.QLEVEL = GetQLevelNum(a);
                                        }
                                        pp.ELEVEL = dsq.Tables[0].Rows[0]["elevel"].ToString() == "" ? "Q" : dsq.Tables[0].Rows[0]["elevel"].ToString();
                                        isqlevel = true;
                                    }
                                    else
                                    {
                                        isqlevel = false;
                                    }
                                    int partid = pbo.AddExcel(p);
                                    
                                    if (partid > 0)
                                    {
                                        int pid = 0;
                                        num++;
                                        pp.PARTID = partid;
                                        pp.STATUS = 0;
                                        pp.PPARDATE = DateTime.Now;
                                        if (isqlevel)
                                          pid=  ppbo.AddPPofQlevel(pp);
                                        else
                                      pid=  ppbo.AddPP(pp);
                                        lbo.WriteLog(CurrentUser.Id, "Import Part:Part No.:" + p.BDNTPARTNO,pid.ToString()); 
                                        

                                        //////将QE加入到集合中
                                        int qe = 0;
                                        foreach (qe q in list)
                                        {
                                            if (q.name == pp.Qeuser)
                                            {
                                                qe = 1;
                                                q.num += 1;
                                            }
                                        }
                                        if (qe == 0)
                                        {
                                            list.Add(new qe { name=pp.Qeuser,num=1 });
                                        }
                                        //////将QE加入到集合中

                                        
                                    }
                                    if (sabo.GetList(" lower(USERNAME)='" + dr[7].ToString().Trim().ToLower() + "' ").Tables[0].Rows.Count == 0)
                                    {
                                        SupplierAdmin sa = new SupplierAdmin();
                                        sa.PASSWORD = "123456";
                                        sa.SUPPLIERID = int.Parse(dr["supplierid"].ToString().Trim());
                                        sa.USERNAME = dr[7].ToString().Trim().ToLower();
                                        int said = sabo.Add(sa);
                                        if (mail15 != null && said > 0)
                                        {
                                            try
                                            {
                                                base.SendEmailToUser(sa.USERNAME, mail15.TITLE, mail15.CONTENT.Replace("{Username}", sa.USERNAME).Replace("{Password}", sa.PASSWORD), null);
                                                lbo.WriteLog(CurrentUser.Id, "Send Email:" + sa.USERNAME);
                                            }
                                            catch (Exception ex)
                                            {
                                                lbo.WriteLog(CurrentUser.Id, "Send Email:" + ex.Message.ToString());
                                            }
                                        }
                                    }

                                }
                                //
                            }
                            else
                            {
                                pbo.UpdatePartCheck(dr[1].ToString(), dr[6].ToString(), dr[4].ToString(),dr[2].ToString(),dr[5].ToString()); //修改已存在的part的状态
                            }
                        }
                    }

                    if (mail16 != null)
                    {
                        foreach (qe q in list)
                        {
                            try
                            {
                                //if (!sendQE.Contains(pp.Qeuser))
                                //{


                                base.SendEmailToUser(q.name, mail16.TITLE.Replace("{Num}", q.num.ToString()), mail16.CONTENT, null);
                                //sendQE += pp.Qeuser + ";";
                                lbo.WriteLog(CurrentUser.Id, "Send Email:" + q.name);


                                //}
                            }
                            catch (Exception ex)
                            {
                                lbo.WriteLog(CurrentUser.Id, "Send Email:" + ex.Message.ToString());
                            }
                        }
                    }

                    //ppbo.UpdateTerminationForCheck(); //将已不存在的ppar禁用
                    if (num > 0)
                    {
                        ppbo.UpdateBdntPartNoX();
                        lbo.WriteLog(CurrentUser.Id, "ImportPart:"+num); 
                        AlertMessage("Saved(" + num + ")");
                    }
                    else
                        AlertMessage("Saved(" + 0 + ")");
                    btnSave.Visible = false;
                    lblMessage.Text = message;
                }
            }
            catch (Exception ex)
            {
                AlertMessage(ex.Message.ToString());
            }
            finally
            {
                Session["dsexcel"] = null;
            }
        }

        private string GetQLevelNum(int num)
        {
            string value = "";
            switch (num.ToString().Length)
            {
                case 1:
                    value += "00" + num;
                    break;
                case 2:
                    value += "0" + num;
                    break;
                case 3:
                    value += num.ToString();
                    break;
                default:
                    value += "001";
                    break;
            }
            return value;
        }
        
    }
}